﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Data;
using System.Reflection;
using Models;

namespace DAL
{
    /// <summary>
    /// 分页列表
    /// </summary>
    public class PagingList<T> : List<T>
    {
        /// <summary>
        /// 不分页的情况下总记录数
        /// </summary>
        public int RecordCount { get; set; }
    }

    /// <summary>
    /// 分页列表 (用于转为json)
    /// </summary>
    public class PagingListForJson<T>
    {
        /// <summary>
        /// 不分页的情况下总记录数
        /// </summary>
        public int total { get; set; }
        /// <summary>
        /// 指定页码下的数据
        /// </summary>
        public List<T> data { get; set; }
    }


    /// <summary>
    /// 通用数据访问类
    /// </summary>
    public class BaseDAL
    {
        /// <summary>
        /// 添加数据
        /// </summary>
        public static int Insert(Object entity)
        {
            //检查
            if (entity == null)
                throw new Exception("参数不能为null");

            //得到类型信息
            Type type = entity.GetType();
            PropertyInfo[] ptys = type.GetProperties();
            PropertyInfo ptykey = null;
            foreach (PropertyInfo pty in ptys) //查找主键字段
            {
                if (pty.GetCustomAttributes(typeof(KeyAttribute), true).Length > 0)
                {
                    ptykey = pty;
                    break;
                }
            }
            if (ptykey == null)
                throw new Exception("对象必须设置[key]元属性。");
            if (ptykey.PropertyType != typeof(int) &&
                ptykey.PropertyType != typeof(int?))
                throw new Exception("主键必须是int或int?类型。");

            //生成SQL语句
            //insert into 表名 (字段1,字段2...) values (@变量1,@变量2...)
            StringBuilder sb = new StringBuilder();
            sb.Append("insert into ");
            sb.Append(type.Name);
            sb.Append(" (");
            foreach (PropertyInfo pty in ptys)
            {
                if (pty != ptykey)
                {
                    sb.Append(pty.Name);
                    sb.Append(",");
                }
            }
            sb.Remove(sb.Length - 1, 1); //去除最后一个多余的逗号
            sb.Append(" ) values (");
            foreach (PropertyInfo pty in ptys)
            {
                if (pty != ptykey)
                {
                    sb.Append("@");
                    sb.Append(pty.Name);
                    sb.Append(",");
                }
            }
            sb.Remove(sb.Length - 1, 1); //去除最后一个多余的逗号
            sb.Append(" ) select @@identity");

            //准备标量参数
            List<SqlParameter> listPars = new List<SqlParameter>();
            foreach (PropertyInfo pty in ptys)
            {
                if (pty != ptykey)
                {
                    SqlParameter par = new SqlParameter();
                    par.ParameterName = "@" + pty.Name;
                    par.Value = pty.GetValue(entity, null) ?? DBNull.Value;
                    listPars.Add(par);
                }
            }

            //执行添加语句
            object newId = ExecuteScalar(sb.ToString(), listPars.ToArray());
            if (newId == DBNull.Value)
            {//添加失败
                return 0; //受影响的行数
            }
            else
            {//添加成功
                
                //将新产生的ID，设置到实体对象的ID属性里
                ptykey.SetValue(entity, int.Parse(newId.ToString()), null);

                return 1; //受影响的行数
            }
        }

        /// <summary>
        /// 修改数据
        /// </summary>
        public static int Update(Object entity)
        {
            //检查
            if (entity == null)
                throw new Exception("参数不能为null");

            //得到类型信息
            Type type = entity.GetType();
            PropertyInfo[] ptys = type.GetProperties();
            PropertyInfo ptykey = null;
            foreach (PropertyInfo pty in ptys) //查找主键字段
            {
                if (pty.GetCustomAttributes(typeof(KeyAttribute), true).Length > 0)
                {
                    ptykey = pty;
                    break;
                }
            }
            if (ptykey == null)
                throw new Exception("对象必须设置[key]元属性。");
            if (ptykey.PropertyType != typeof(int) &&
                ptykey.PropertyType != typeof(int?))
                throw new Exception("主键必须是int或int?类型。");

            //生成SQL语句
            //update 表名 set 字段1=@变量1, 字段2=@变量2 where 主键=@主键
            StringBuilder sb = new StringBuilder();
            sb.Append("update ");
            sb.Append(type.Name);
            sb.Append(" set ");
            foreach (PropertyInfo pty in ptys)
            {
                if (pty != ptykey)
                {
                    sb.Append(pty.Name);
                    sb.Append("=@");
                    sb.Append(pty.Name);
                    sb.Append(",");
                }
            }
            sb.Remove(sb.Length - 1, 1); //删除最后一个多余的逗号
            sb.Append(" where ");
            sb.Append(ptykey.Name);
            sb.Append("=@");
            sb.Append(ptykey.Name);

            //准备标量参数集合
            List<SqlParameter> listPars = new List<SqlParameter>();
            foreach (PropertyInfo pty in ptys)
            {
                SqlParameter par = new SqlParameter();
                par.ParameterName = "@" + pty.Name;
                par.Value = pty.GetValue(entity, null) ?? DBNull.Value;
                listPars.Add(par);
            }

            //执行
            return ExecuteNonQuery(sb.ToString(), listPars.ToArray());
        }

        /// <summary>
        /// 删除数据
        /// </summary>
        public static int Delete(Object entity)
        {
            //检查
            if (entity == null)
                throw new Exception("参数不能为null");

            //得到类型信息
            Type type = entity.GetType();
            PropertyInfo[] ptys = type.GetProperties();
            PropertyInfo ptykey = null;
            foreach (PropertyInfo pty in ptys) //查找主键字段
            {
                if (pty.GetCustomAttributes(typeof(KeyAttribute), true).Length > 0)
                {
                    ptykey = pty;
                    break;
                }
            }
            if (ptykey == null)
                throw new Exception("对象必须设置[key]元属性。");
            if (ptykey.PropertyType != typeof(int) &&
                ptykey.PropertyType != typeof(int?))
                throw new Exception("主键必须是int或int?类型。");

            //生成SQL语句
            //delete 表名 where 主键=@主键
            StringBuilder sb = new StringBuilder();
            sb.Append("delete ");
            sb.Append(type.Name);
            sb.Append(" where ");
            sb.Append(ptykey.Name);
            sb.Append("=@");
            sb.Append(ptykey.Name);

            //准备标量参数
            SqlParameter[] pars = new SqlParameter[1];
            pars[0] = new SqlParameter("@" + ptykey.Name,
                ptykey.GetValue(entity, null) ?? DBNull.Value);

            //执行
            return ExecuteNonQuery(sb.ToString(), pars);
        }

        /// <summary>
        /// 删除数据
        /// </summary>
        public static int Delete<T>(int id)
        {
            //得到类型信息
            Type type = typeof(T);
            PropertyInfo[] ptys = type.GetProperties();
            PropertyInfo ptykey = null;
            foreach (PropertyInfo pty in ptys) //查找主键字段
            {
                if (pty.GetCustomAttributes(typeof(KeyAttribute), true).Length > 0)
                {
                    ptykey = pty;
                    break;
                }
            }
            if (ptykey == null)
                throw new Exception("对象必须设置[key]元属性。");
            if (ptykey.PropertyType != typeof(int) &&
                ptykey.PropertyType != typeof(int?))
                throw new Exception("主键必须是int或int?类型。");

            //生成SQL语句
            //delete 表名 where 主键=@主键
            StringBuilder sb = new StringBuilder();
            sb.Append("delete ");
            sb.Append(type.Name);
            sb.Append(" where ");
            sb.Append(ptykey.Name);
            sb.Append("=@");
            sb.Append(ptykey.Name);

            //准备标量参数
            SqlParameter[] pars = new SqlParameter[1];
            pars[0] = new SqlParameter("@" + ptykey.Name, id);

            //执行
            return ExecuteNonQuery(sb.ToString(), pars);
        }

        /// <summary>
        /// 查询所有
        /// </summary>
        public static List<T> SelectAll<T>()
        {
            //得到类型信息
            Type type = typeof(T);
            PropertyInfo[] ptys = type.GetProperties();
            PropertyInfo ptykey = null;
            foreach (PropertyInfo pty in ptys) //查找主键字段
            {
                if (pty.GetCustomAttributes(typeof(KeyAttribute), true).Length > 0)
                {
                    ptykey = pty;
                    break;
                }
            }
            if (ptykey == null)
                throw new Exception("对象必须设置[key]元属性。");
            if (ptykey.PropertyType != typeof(int) &&
                ptykey.PropertyType != typeof(int?))
                throw new Exception("主键必须是int或int?类型。");

            //生成SQL语句
            //select * from 表名 order by 主键 asc
            StringBuilder sb = new StringBuilder();
            sb.Append("select *  from ");
            sb.Append(type.Name);
            sb.Append(" order by ");
            sb.Append(ptykey.Name);
            sb.Append(" desc");

            //创建结果集
            List<T> list = new List<T>();

            //执行Reader查询，并循环读取数据
            using(SqlDataReader sdr = ExecuteReader(sb.ToString()))
            {
                while (sdr.Read())
                {
                    //根据泛型创建一个实体对象
                    T entity = Activator.CreateInstance<T>();
                    //为每一个属性赋值
                    foreach (PropertyInfo pty in ptys)
                    {
                        //得到数据库中查询出来的值
                        object value = sdr[pty.Name];
                        //此值有可能是DBNull
                        if (value == DBNull.Value)
                            value = default(T); //如果是DBNull则改为类型的默认值
                        //为属性赋值
                        pty.SetValue(entity, value, null);
                    }
                    //将实体对象添加到集合中
                    list.Add(entity);
                }
            }
            //返回结果集
            return list;
        }

        /// <summary>
        /// 按SQL语句查询
        /// </summary>
        public static List<T> Select<T>(string sql, SqlParameter[] pars)
        {
            //得到类型信息
            Type type = typeof(T);
            PropertyInfo[] ptys = type.GetProperties();
            
            //创建结果集
            List<T> list = new List<T>();

            //执行Reader查询，并循环读取数据
            using (SqlDataReader sdr = ExecuteReader(sql, pars))
            {
                while (sdr.Read())
                {
                    //根据泛型创建一个实体对象
                    T entity = Activator.CreateInstance<T>();
                    //为每一个属性赋值
                    foreach (PropertyInfo pty in ptys)
                    {
                        //得到数据库中查询出来的值
                        object value = sdr[pty.Name];
                        //此值有可能是DBNull
                        if (value == DBNull.Value)
                            value = default(T); //如果是DBNull则改为类型的默认值
                        //为属性赋值
                        pty.SetValue(entity, value, null);
                    }
                    //将实体对象添加到集合中
                    list.Add(entity);
                }
            }
            //返回结果集
            return list;
        }

        /// <summary>
        /// 按SQL语句查询
        /// </summary>
        public static List<T> Select<T>(string sql)
        {
            return Select<T>(sql, null);
        }

        /// <summary>
        /// 根据主键值，查询单个实体对象
        /// </summary>
        public static T Find<T>(int id)
        {
            //得到类型信息
            Type type = typeof(T);
            PropertyInfo[] ptys = type.GetProperties();
            PropertyInfo ptykey = null;
            foreach (PropertyInfo pty in ptys) //查找主键字段
            {
                if (pty.GetCustomAttributes(typeof(KeyAttribute), true).Length > 0)
                {
                    ptykey = pty;
                    break;
                }
            }
            if (ptykey == null)
                throw new Exception("对象必须设置[key]元属性。");
            if (ptykey.PropertyType != typeof(int) &&
                ptykey.PropertyType != typeof(int?))
                throw new Exception("主键必须是int或int?类型。");

            //生成SQL语句
            //select * from 表名 where 主键=@主键
            StringBuilder sb = new StringBuilder();
            sb.Append("select *  from ");
            sb.Append(type.Name);
            sb.Append(" where ");
            sb.Append(ptykey.Name);
            sb.Append("=@");
            sb.Append(ptykey.Name);

            //定义标量变量
            SqlParameter[] pars = new SqlParameter[1];
            pars[0] = new SqlParameter("@" + ptykey.Name, id);

            //执行Reader查询，并读取数据
            using (SqlDataReader sdr = ExecuteReader(sb.ToString(), pars))
            {
                if (sdr.Read())
                {//查到了，返回实体

                    //根据泛型创建一个实体对象
                    T entity = Activator.CreateInstance<T>();
                    //为每一个属性赋值
                    foreach (PropertyInfo pty in ptys)
                    {
                        //得到数据库中查询出来的值
                        object value = sdr[pty.Name];
                        //此值有可能是DBNull
                        if (value == DBNull.Value)
                            value = default(T); //如果是DBNull则改为类型的默认值
                        //为属性赋值
                        pty.SetValue(entity, value, null);
                    }
                    //返回实体
                    return entity;
                }
                else
                {//查询不到，返回null

                    //相当于，返回null
                    return default(T);
                }
            }
        }
        /// <summary>
        /// 根据主键值，查询单个实体对象
        /// </summary>
        public static T FindStr<T>(string id)
        {
            //得到类型信息
            Type type = typeof(T);
            PropertyInfo[] ptys = type.GetProperties();
            PropertyInfo ptykey = null;
            foreach (PropertyInfo pty in ptys) //查找主键字段
            {
                if (pty.GetCustomAttributes(typeof(KeyAttribute), true).Length > 0)
                {
                    ptykey = pty;
                    break;
                }
            }
            if (ptykey == null)
                throw new Exception("对象必须设置[key]元属性。");
            if (ptykey.PropertyType != typeof(int) &&
                ptykey.PropertyType != typeof(int?))
                throw new Exception("主键必须是int或int?类型。");

            //生成SQL语句
            //select * from 表名 where 主键=@主键
            StringBuilder sb = new StringBuilder();
            sb.Append("select *  from ");
            sb.Append(type.Name);
            sb.Append(" where ");
            sb.Append(ptykey.Name);
            sb.Append("=@");
            sb.Append(ptykey.Name);

            //定义标量变量
            SqlParameter[] pars = new SqlParameter[1];
            pars[0] = new SqlParameter("@" + ptykey.Name, id);

            //执行Reader查询，并读取数据
            using (SqlDataReader sdr = ExecuteReader(sb.ToString(), pars))
            {
                if (sdr.Read())
                {//查到了，返回实体

                    //根据泛型创建一个实体对象
                    T entity = Activator.CreateInstance<T>();
                    //为每一个属性赋值
                    foreach (PropertyInfo pty in ptys)
                    {
                        //得到数据库中查询出来的值
                        object value = sdr[pty.Name];
                        //此值有可能是DBNull
                        if (value == DBNull.Value)
                            value = default(T); //如果是DBNull则改为类型的默认值
                        //为属性赋值
                        pty.SetValue(entity, value, null);
                    }
                    //返回实体
                    return entity;
                }
                else
                {//查询不到，返回null

                    //相当于，返回null
                    return default(T);
                }
            }
        }
        /// <summary>
        /// 分页查询
        /// </summary>
        public static PagingList<T> Paging<T>(int pageSize, int pageIndex, string whereStr, SqlParameter[] pars, bool isAsc)
        {
            //检查
            if (pageSize <= 0)
                throw new Exception("pageSize必须大于零。");
            if (pageIndex <= 0)
                throw new Exception("pageIndex必须大于零。");

            //得到类型信息
            Type type = typeof(T);
            PropertyInfo[] ptys = type.GetProperties();
            PropertyInfo ptykey = null;
            foreach (PropertyInfo pty in ptys) //查找主键字段
            {
                if (pty.GetCustomAttributes(typeof(KeyAttribute), true).Length > 0)
                {
                    ptykey = pty;
                    break;
                }
            }
            if (ptykey == null)
                throw new Exception("对象必须设置[key]元属性。");
            if (ptykey.PropertyType != typeof(int) &&
                ptykey.PropertyType != typeof(int?))
                throw new Exception("主键必须是int或int?类型。");

            //拼装SQL语句
            StringBuilder sb = new StringBuilder();
            if (pageIndex == 1)
            {
                //select top (@pagesize) * from student 
                //where sex='男' order by id asc

                sb.Append("select top (@pagesize) * from ");
                sb.Append(type.Name);
                if (!string.IsNullOrEmpty(whereStr))
                    sb.Append(" where " + whereStr);
                sb.Append(" order by ");
                sb.Append(ptykey.Name);
                sb.Append(isAsc ? " asc" : " desc");
            }
            else
            {
                //select top (@pagesize) * from student where id>(
                //  select max(ID) from 
                //  (select top ((@pageindex-1)*@pagesize) id from student where sex='男' order by id asc) a
                //) and sex='男' order by id asc

                sb.Append("select top (@pagesize) * from ");
                sb.Append(type.Name);
                sb.Append(" where ");
                sb.Append(ptykey.Name);
                sb.Append(isAsc ? ">(" : "<(");
                sb.Append(" select ");
                sb.Append(isAsc ? "max(" : "min(");
                sb.Append(ptykey.Name);
                sb.Append(") from (select top ((@pageindex-1)*@pagesize) ");
                sb.Append(ptykey.Name);
                sb.Append(" from ");
                sb.Append(type.Name);
                if (!string.IsNullOrEmpty(whereStr))
                    sb.Append(" where " + whereStr);
                sb.Append(" order by ");
                sb.Append(ptykey.Name);
                sb.Append(isAsc ? " asc" : " desc");
                sb.Append(") a )");
                if (!string.IsNullOrEmpty(whereStr))
                    sb.Append(" and (" + whereStr + ") ");
                sb.Append(" order by ");
                sb.Append(ptykey.Name);
                sb.Append(isAsc ? " asc" : " desc");
            }

            //准备标量变量集合
            List<SqlParameter> listPars = new List<SqlParameter>();
            listPars.Add(new SqlParameter("@pagesize", pageSize));
            listPars.Add(new SqlParameter("@pageindex", pageIndex));
            if (pars != null)
                listPars.AddRange(pars);

            //创建结果集
            PagingList<T> list = new PagingList<T>();

            //执行Reader查询，并循环读取数据
            using (SqlDataReader sdr = ExecuteReader(sb.ToString(), listPars.ToArray()))
            {
                while (sdr.Read())
                {
                    //根据泛型创建一个实体对象
                    T entity = Activator.CreateInstance<T>();
                    //为每一个属性赋值
                    foreach (PropertyInfo pty in ptys)
                    {
                        //得到数据库中查询出来的值
                        object value = sdr[pty.Name];
                        //此值有可能是DBNull
                        if (value == DBNull.Value)
                            value = default(T); //如果是DBNull则改为类型的默认值
                        //为属性赋值
                        pty.SetValue(entity, value, null);
                    }
                    //将实体对象添加到集合中
                    list.Add(entity);
                }
            }

            //查询不分页的情况下，总记录数
            //装SQL语句
            //select count(*) from 表名 where 条件
            sb = new StringBuilder();
            sb.Append("select count(*) from ");
            sb.Append(type.Name);
            if (!string.IsNullOrEmpty(whereStr))
                sb.Append(" where " + whereStr);
            //准备标量参数集合
            listPars = new List<SqlParameter>();
            if (pars != null)
            {
                for (int i = 0; i < pars.Length; i++)
                {
                    string parName = pars[i].ParameterName;
                    object value = pars[i].Value;
                    listPars.Add(new SqlParameter(parName, value));
                }
            }
            //执行单值查询，查出不分页的情况下，总记录数
            list.RecordCount = int.Parse(ExecuteScalar(sb.ToString(), listPars.ToArray()).ToString());

            //返回结果集
            return list;
        }

        /// <summary>
        /// 分页查询
        /// </summary>
        public static PagingList<T> Paging<T>(int pageSize, int pageIndex, string whereStr, SqlParameter[] pars)
        {
            return Paging<T>(pageSize, pageIndex, whereStr, pars, true);
        }

        /// <summary>
        /// 分页查询
        /// </summary>
        public static PagingList<T> Paging<T>(int pageSize, int pageIndex, string whereStr)
        {
            return Paging<T>(pageSize, pageIndex, whereStr, null, true);
        }

        /// <summary>
        /// 分页查询
        /// </summary>
        public static PagingList<T> Paging<T>(int pageSize, int pageIndex)
        {
            return Paging<T>(pageSize, pageIndex, "", null, true);
        }

        /// <summary>
        /// 分页查询
        /// </summary>
        public static PagingList<T> Paging<T>(int pageSize, int pageIndex, string whereStr, bool isAsc)
        {
            return Paging<T>(pageSize, pageIndex, whereStr, null, isAsc);
        }

        /// <summary>
        /// 分页查询
        /// </summary>
        public static PagingList<T> Paging<T>(int pageSize, int pageIndex, bool isAsc)
        {
            return Paging<T>(pageSize, pageIndex, "", null, isAsc);
        }


        /// <summary>
        /// 数据库连接字符串
        /// </summary>
        public static string ConnectionString { get; set; }

        /// <summary>
        /// 静态构造方法
        /// </summary>
        static BaseDAL()
        {
            //从配置文件中读取数据库连接字符串
            ConnectionString =System.Configuration.ConfigurationManager.ConnectionStrings["conn"].ConnectionString;
        }

        /// <summary>
        /// 执行增删改SQL语句，并返回受影响的行数。
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="pars">参数列表</param>
        /// <returns>受影响的行数</returns>
        public static int ExecuteNonQuery(string sql, SqlParameter[] pars)
        {
            using (SqlConnection conn = new SqlConnection(ConnectionString))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(sql, conn);
                if (pars != null)
                    cmd.Parameters.AddRange(pars);
                return cmd.ExecuteNonQuery();
            }
        }
        /// <summary>
        /// 执行增删改SQL语句，并返回受影响的行数。
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <returns>受影响的行数</returns>
        public static int ExecuteNonQuery(string sql)
        {
            return ExecuteNonQuery(sql, null);
        }


        /// <summary>
        /// 执行查询SQL语句，并返回数据读取器。
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="pars">参数列表</param>
        /// <returns>数据读取器</returns>
        public static SqlDataReader ExecuteReader(string sql, SqlParameter[] pars)
        {
            SqlConnection conn = new SqlConnection(ConnectionString);
            conn.Open();
            SqlCommand cmd = new SqlCommand(sql, conn);
            if (pars != null)
                cmd.Parameters.AddRange(pars);
            return cmd.ExecuteReader(CommandBehavior.CloseConnection);
        }
        /// <summary>
        /// 执行查询SQL语句，并返回数据读取器。
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <returns>数据读取器</returns>
        public static SqlDataReader ExecuteReader(string sql)
        {
            return ExecuteReader(sql, null);
        }


        /// <summary>
        /// 执行查询SQL语句，并返回数据集。
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="pars">参数列表</param>
        /// <returns>数据集</returns>
        public static DataSet ExecuteDataSet(string sql, SqlParameter[] pars)
        {
            SqlConnection conn = new SqlConnection(ConnectionString);
            SqlDataAdapter sda = new SqlDataAdapter(sql, conn);
            if (pars != null)
                sda.SelectCommand.Parameters.AddRange(pars);
            DataSet ds = new DataSet();
            sda.Fill(ds);
            return ds;
        }
        /// <summary>
        /// 执行查询SQL语句，并返回数据集。
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <returns>数据集</returns>
        public static DataSet ExecuteDataSet(string sql)
        {
            return ExecuteDataSet(sql, null);
        }


        /// <summary>
        /// 执行查询SQL语句，并返回数据表。
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="pars">参数列表</param>
        /// <returns>数据表</returns>
        public static DataTable ExecuteDataTable(string sql, SqlParameter[] pars)
        {
            DataSet ds = ExecuteDataSet(sql, pars);
            if (ds != null && ds.Tables.Count > 0)
                return ds.Tables[0];
            else
                return null;
        }
        /// <summary>
        /// 执行查询SQL语句，并返回数据表。
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <returns>数据表</returns>
        public static DataTable ExecuteDataTable(string sql)
        {
            return ExecuteDataTable(sql, null);
        }


        /// <summary>
        /// 执行查询SQL语句，并返回数据行。
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="pars">参数列表</param>
        /// <returns>数据行</returns>
        public static DataRow ExecuteDataRow(string sql, SqlParameter[] pars)
        {
            DataTable dt = ExecuteDataTable(sql, pars);
            if (dt == null || dt.Rows.Count == 0)
                return null;
            else
                return dt.Rows[0];
        }
        /// <summary>
        /// 执行查询SQL语句，并返回数据行。
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <returns>数据行</returns>
        public static DataRow ExecuteDataRow(string sql)
        {
            return ExecuteDataRow(sql, null);
        }


        /// <summary>
        /// 执行查询SQL语句，并返回单值。
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <param name="pars">参数列表</param>
        /// <returns>单个值</returns>
        public static object ExecuteScalar(string sql, SqlParameter[] pars)
        {
            using (SqlConnection conn = new SqlConnection(ConnectionString))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(sql, conn);
                if (pars != null)
                    cmd.Parameters.AddRange(pars);
                return cmd.ExecuteScalar();
            }
        }
        /// <summary>
        /// 执行查询SQL语句，并返回单值。
        /// </summary>
        /// <param name="sql">SQL语句</param>
        /// <returns>单个值</returns>
        public static object ExecuteScalar(string sql)
        {
            return ExecuteScalar(sql, null);
        }
    }
}



